package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.FaultageQueryParams;
import com.css.fxfzypg.modules.baseData.repository.FaultageRepository;
import com.css.fxfzypg.modules.baseData.entity.FaultageEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-17 14:24
 */
@Repository
public class FaultageRepositoryImpl implements FaultageRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public FaultageEntity saveFaultage(FaultageEntity faultageEntity) {
        String sql = "";
        sql = "insert into data_faultage values(:fauId,:fauNumber, :fauName, :fauDirection, :fauCreatetime,:fauUpdatetime, :fauIsdelete) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(faultageEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return faultageEntity;
    }

    @Override
    public FaultageEntity findFaultageById(String dataFaultageId) {
        return jdbcTemplate.queryForObject("select u.* from data_faultage u where u.fau_id='" + dataFaultageId + "' and u.fau_isdelete ='" + YNEnum.Y + "'", new BeanPropertyRowMapper<>(FaultageEntity.class));
    }

    @Override
    public List<FaultageEntity> getFaultByTaskId(String taskId) {
        String sql = "SELECT t.*,ST_AsText(t.geom) geomText FROM data_faultage t WHERE ST_Intersects(t.geom," +
                "(select a.geom FROM ypg_preassess_task a " +
                "WHERE a.id = '" + taskId + "')) and t.fau_isdelete ='" + YNEnum.N + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FaultageEntity.class));
    }

    @Override
    public Page<FaultageEntity> getAllFaultageByParams(FaultageQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_faultage u ");

        StringBuilder orderSql = new StringBuilder(" order by u.fau_createtime");

        StringBuilder whereSql = new StringBuilder(" where u.fau_isdelete = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getFauName())) {
            whereSql.append(" and u.fau_name like %" + queryParams.getFauName() + "%");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getFauNumber())) {
            whereSql.append(" and u.fau_number like %" + queryParams.getFauNumber() + "%");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<FaultageEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(FaultageEntity.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<FaultageEntity> faultageEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return faultageEntities;

    }

    @Override
    public void deleteFaultage(List<String> ids, Timestamp currentTimestamp) {
        String sql = "update data_faultage set fau_isdelete = '" + YNEnum.N.toString() + "' ,fau_updatetime ='" + currentTimestamp + "' " +
                " where fau_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<FaultageEntity> exportExcel() {
        return jdbcTemplate.query("select  u.* from data_faultage u where u.fau_isdelete='" + YNEnum.Y.toString() + "'", new BeanPropertyRowMapper<>(FaultageEntity.class));
    }

    @Override
    public void importExcel(List<FaultageEntity> faultageEntities) {
        jdbcTemplate.batchUpdate("insert into data_faultage (fau_id,fau_number,fau_name,fau_direction,fau_createtime,fau_isdelete)values(?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, faultageEntities.get(i).getFauId());
                ps.setString(2, faultageEntities.get(i).getFauNumber());
                ps.setString(3, faultageEntities.get(i).getFauName());
                ps.setInt(4, faultageEntities.get(i).getFauDirection());
                java.sql.Date createDate = new java.sql.Date(faultageEntities.get(i).getFauCreatetime().getTime());
                ps.setDate(5, createDate);
                ps.setString(6, faultageEntities.get(i).getFauIsdelete());
            }

            @Override
            public int getBatchSize() {
                return faultageEntities.size();
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_faultage where county = '" + county + "' and city = '" + city + "' and province = '" + province + "'  or (province ='"+province+"' and city ='' and county ='')  and fau_isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_faultage where city = '" + city + "' and province = '" + province + "' and fau_isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_faultage where province = '" + province + "' and fau_isdelete = '0'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_faultage where province = ? and (TO_CHAR(fau_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(fau_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and fau_isdelete = '0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
